using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace TestSystem.DAL.TestSystem
{
    public class SubjectDataAccess : DataAccess
    {
        public static void UpdateSubject(int subjectID, string subjectName, string description, bool deleted)
        {
            SqlParameter [] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID),
                    new SqlParameter("@SubjectName", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectName), 
                    new SqlParameter("@Description", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, description),
                    new SqlParameter("@Deleted", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, deleted)
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Subject_Update", paramsArray);
        }

        public static int InsertSubject(string subjectName, string description)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SubjectName", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectName), 
                    new SqlParameter("@Description", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, description)
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Subject_Insert", paramsArray));
        }
        
        public static IDataReader GetAll(bool selectDeleted, int lowerBound, int upperBound)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SelectDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, selectDeleted),
                    new SqlParameter("@LowerBound", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, lowerBound),
                    new SqlParameter("@UpperBound", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, upperBound)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Subject_GetAll", paramsArray);
        }
        
        public static IDataReader GetBySubjectID(int subjectID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Subject_GetBySubjectID", paramsArray);
        }

        public static void DeleteSubject(int subjectID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@SubjectID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, subjectID)
                };
            SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Subject_Delete", paramsArray);
        }

        public static int GetCount()
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Subject_GetCount"));
        }
    }
}
